/*** 
This do-file creates the table 'Mechanisms Underlying the Persistent 
Reduction in Low-Wage Employment: Hysteresis vs. Current Conditions'. 
***/

*-------------------------------------------------------------------------------
* Set up
*-------------------------------------------------------------------------------

* Set $root 
project figstabs, root
if (r(buildrunning)==0) include "${root}/code/config_interactive.do"

* Set globals
project, uses("${root}/code/set_globals.do")
include "${root}/code/set_globals.do"

* Create folders
cap mkdir "${root}/results/Employment analysis"

* Set controls
local covid_controls_dec2021 currcases_end2021
local covid_controls_jul2020 cumcases_jul2020
local dem_controls share_white percent_foreign_born pop_25_54 female
local revenue_month apr

*-------------------------------------------------------------------------------
* Load data
*-------------------------------------------------------------------------------
project, uses("${root}/results/Employment, COVID, Covars - County.dta")
use "${root}/results/Employment, COVID, Covars - County.dta" , clear

* Drop CA, MA, NY
cap gen statefips = floor(countyfips / 1000)
drop if inlist(statefips, 6, 25, 36)

* Get rent in thousands
replace med_2br_2018 = med_2br_2018 / 1000

* Get difference in max ui weeks 
gen ui_max_weeks_diff = ui_max_weeks_dec21 - ui_max_weeks_jan20
tab ui_max_weeks_diff

* Thousands for covid cases
replace currcases_end2021 = currcases_end2021 / 1000

* Drop unnecessary vars and obs
drop if countyfips == . | countyfips >= 70000

* Col 1: Emp in Dec 2021 against initial shock in revenue
reg emp_incq1_dec2021 med_2br_2018 [w = pop_2018], r

local col1_obs = e(N)
local col1_beta = _b[med_2br_2018]
local col1_se = _se[med_2br_2018]
local col1_r2 = e(r2)

gen col1_sample= e(sample)

* Col 2: Add contemporaneous COVID controls and UI
reg emp_incq1_dec2021  med_2br_2018 `covid_controls_dec2021' ui_max_weeks_dec21 [w = pop_2018], r

local col2_obs = e(N)
local col2_beta = _b[med_2br_2018]
local col2_se = _se[med_2br_2018]
local col2_beta_covid = _b[currcases_end2021]
local col2_se_covid = _se[currcases_end2021]
local col2_beta_ui = _b[ui_max_weeks_dec21]
local col2_se_ui = _se[ui_max_weeks_dec21]
local col2_r2 = e(r2)

gen col2_sample= e(sample)

sum med_2br_2018 [w = pop_2018] if col2_sample == 1, d
local col2_mean_rent = `r(mean)'
local col2_exp_rent = `col2_beta' * `col2_mean_rent'
local col2_p10_rent = `r(p10)'
local col2_p90_rent = `r(p90)'
local col2_p90p10_rent = `col2_p90_rent' - `col2_p10_rent'
local col2_p90p10_pred_rent = `col2_p90p10_rent' * `col2_beta'
di "Rent: `col2_p90p10_pred_rent'"

sum currcases_end2021 [w = pop_2018] if col2_sample == 1, d
local col2_mean_covid = `r(mean)'
local col2_exp_covid = `col2_beta_covid' * `col2_mean_covid'
local col2_p10_covid = `r(p10)'
local col2_p90_covid = `r(p90)'
local col2_p90p10_covid = `col2_p90_covid' - `col2_p10_covid'
local col2_p90p10_pred_covid = `col2_p90p10_covid' * `col2_beta_covid'
di "COVID: `col2_p90p10_pred_covid'"

sum ui_max_weeks_diff [w = pop_2018] if col2_sample == 1, d
local col2_mean_ui_diff = `r(mean)'
local col2_exp_ui = `col2_beta_ui' * `col2_mean_ui_diff'
local col2_p10_ui = `r(p10)'
local col2_p90_ui = `r(p90)'
local col2_p90p10_ui = `col2_p90_ui' - `col2_p10_ui'
local col2_p90p10_pred_ui = `col2_p90p10_ui' * `col2_beta_ui'
di "UI: `col2_p90p10_pred_ui'"

sum ui_max_weeks_dec21 [w = pop_2018] if col2_sample == 1, d
local col2_mean_ui = `r(mean)'

* Col 3: Add demographic controls
reg emp_incq1_dec2021  med_2br_2018 `covid_controls_dec2021' ui_max_weeks_dec21 `dem_controls'  [w = pop_2018], r

predict predicted_value 

local col3_obs = e(N)
local col3_beta = _b[med_2br_2018]
local col3_se = _se[med_2br_2018]
local col3_beta_covid = _b[currcases_end2021]
local col3_se_covid = _se[currcases_end2021]
local col3_beta_ui = _b[ui_max_weeks_dec21]
local col3_se_ui = _se[ui_max_weeks_dec21]
local col3_r2 = e(r2)

gen col3_sample= e(sample)
assert col1_sample == col2_sample 
assert col1_sample == col3_sample
assert col2_sample == col3_sample

* Calculate predicted difference in employment using 90th percentile covar vs 10th percentile covar
sum med_2br_2018 [w = pop_2018] if col3_sample == 1, d
local col3_mean_rent = `r(mean)'
local col3_exp_rent = `col3_beta' * `col3_mean_rent'
local col3_p10_rent = `r(p10)'
local col3_p5_rent = `r(p5)'
local col3_p90_rent = `r(p90)'
local col3_p90p10_rent = `col3_p90_rent' - `col3_p10_rent'
local col3_p90p10_pred_rent = `col3_p90p10_rent' * `col3_beta'

di "Rent p5: `col3_p5_rent'" 
di "Rent p10: `col3_p10_rent'"
di "Rent p90: `col3_p90_rent'"
di "Rent: `col3_p90p10_pred_rent'"

* predicted employment change for bottom 5 and 10 percent rent
sum predicted_value [w = pop_2018] if col3_sample == 1 & med_2br_2018 < `col3_p5_rent'
local pred_bottom5 = `r(mean)'
sum predicted_value [w = pop_2018] if col3_sample == 1 & med_2br_2018 < `col3_p10_rent'
local pred_bottom10 = `r(mean)'

di `pred_bottom5'
di `pred_bottom10'
sum currcases_end2021 [w = pop_2018] if col3_sample == 1, d
local col3_mean_covid = `r(mean)'
local col3_exp_covid = `col3_beta_covid' * `col3_mean_covid'
local col3_p10_covid = `r(p10)'
local col3_p90_covid = `r(p90)'
local col3_p90p10_covid = `col3_p90_covid' - `col3_p10_covid'
local col3_p90p10_pred_covid = `col3_p90p10_covid' * `col3_beta_covid'

di "COVID p10: `col3_p10_covid'"
di "COVID p90: `col3_p90_covid'"
di "COVID: `col3_p90p10_pred_covid'"
di "COVID sd: `col3_sd_covid'"
di "COVID sd calculation: `col3_sd_pred_covid'"
di "COVID iqr: `col3_iqr_covid'"
di "COVID iqr calculation: `col3_iqr_pred_covid'"

sum ui_max_weeks_diff [w = pop_2018] if col3_sample == 1, d
local col3_mean_ui_diff = `r(mean)'
local col3_exp_ui = `col3_beta_ui' * `col3_mean_ui_diff'
local col3_p10_ui = `r(p10)'
local col3_p90_ui = `r(p90)'
local col3_p90p10_ui = `col3_p90_ui' - `col3_p10_ui'
local col3_p90p10_pred_ui = `col3_p90p10_ui' * `col3_beta_ui'

di "UI p10: `col3_p10_ui'"
di "UI p90: `col3_p90_ui'"
di "UI: `col3_p90p10_pred_ui'"

sum ui_max_weeks_dec21 [w = pop_2018] if col3_sample == 1, d
local col3_mean_ui = `r(mean)'
local col3_p10_ui = `r(p10)'
local col3_p90_ui = `r(p90)'
local col3_p90p10_ui = `col3_p90_ui' - `col3_p10_ui'
local col3_p90p10_pred_ui = `col3_p90p10_ui' * `col3_beta_ui'
di "UI: `col3_p90p10_pred_ui'"

* Col 4: Use below median wage employment instead of Q1
reg emp_incbelowmed_dec2021  med_2br_2018 `covid_controls_dec2021' ui_max_weeks_dec21 `dem_controls'  [w = pop_2018], r

local col4_obs = e(N)
local col4_beta = _b[med_2br_2018]
local col4_se = _se[med_2br_2018]
local col4_beta_covid = _b[currcases_end2021]
local col4_se_covid = _se[currcases_end2021]
local col4_beta_ui = _b[ui_max_weeks_dec21]
local col4_se_ui = _se[ui_max_weeks_dec21]
local col4_r2 = e(r2)

gen col4_sample= e(sample)

sum med_2br_2018 [w = pop_2018] if col4_sample == 1, d
local col4_mean_rent = `r(mean)'
local col4_exp_rent = `col4_beta' * `col4_mean_rent'
local col4_p10_rent = `r(p10)'
local col4_p90_rent = `r(p90)'
local col4_p90p10_rent = `col4_p90_rent' - `col4_p10_rent'
local col4_p90p10_pred_rent = `col4_p90p10_rent' * `col4_beta'
di "Rent: `col4_p90p10_pred_rent'"

sum currcases_end2021 [w = pop_2018] if col4_sample == 1, d
local col4_mean_covid = `r(mean)'
local col4_exp_covid = `col4_beta_covid' * `col4_mean_covid'
local col4_p10_covid = `r(p10)'
local col4_p90_covid = `r(p90)'
local col4_p90p10_covid = `col4_p90_covid' - `col4_p10_covid'
local col4_p90p10_pred_covid = `col4_p90p10_covid' * `col4_beta_covid'
di "COVID: `col4_p90p10_pred_covid'"

sum ui_max_weeks_diff [w = pop_2018] if col4_sample == 1, d
local col4_mean_ui_diff = `r(mean)'
local col4_exp_ui = `col4_beta_ui' * `col4_mean_ui_diff'
local col4_p10_ui = `r(p10)'
local col4_p90_ui = `r(p90)'
local col4_p90p10_ui = `col4_p90_ui' - `col4_p10_ui'
local col4_p90p10_pred_ui = `col4_p90p10_ui' * `col4_beta_ui'
di "UI: `col4_p90p10_pred_ui'"

sum ui_max_weeks_dec21 [w = pop_2018] if col4_sample == 1, d
local col4_mean_ui = `r(mean)'

* Col 5: Use Q4 employment
reg emp_incq4_dec2021  med_2br_2018 `covid_controls_dec2021' ui_max_weeks_dec21 `dem_controls'  [w = pop_2018], r

local col5_obs = e(N)
local col5_beta = _b[med_2br_2018]
local col5_se = _se[med_2br_2018]
local col5_beta_covid = _b[currcases_end2021]
local col5_se_covid = _se[currcases_end2021]
local col5_beta_ui = _b[ui_max_weeks_dec21]
local col5_se_ui = _se[ui_max_weeks_dec21]
local col5_r2 = e(r2)

gen col5_sample= e(sample)

sum med_2br_2018 [w = pop_2018] if col5_sample == 1, d
local col5_mean_rent = `r(mean)'
local col5_exp_rent = `col5_beta' * `col5_mean_rent'
local col5_p10_rent = `r(p10)'
local col5_p90_rent = `r(p90)'
local col5_p90p10_rent = `col5_p90_rent' - `col5_p10_rent'
local col5_p90p10_pred_rent = `col5_p90p10_rent' * `col5_beta'
di "Rent: `col5_p90p10_pred_rent'"

sum currcases_end2021 [w = pop_2018] if col5_sample == 1, d
local col5_mean_covid = `r(mean)'
local col5_exp_covid = `col5_beta_covid' * `col5_mean_covid'
local col5_p10_covid = `r(p10)'
local col5_p90_covid = `r(p90)'
local col5_p90p10_covid = `col5_p90_covid' - `col5_p10_covid'
local col5_p90p10_pred_covid = `col5_p90p10_covid' * `col5_beta_covid'
di "COVID: `col5_p90p10_pred_covid'"

sum ui_max_weeks_diff [w = pop_2018] if col5_sample == 1, d
local col5_mean_ui_diff = `r(mean)'
local col5_exp_ui = `col5_beta_ui' * `col5_mean_ui_diff'
local col5_p10_ui = `r(p10)'
local col5_p90_ui = `r(p90)'
local col5_p90p10_ui = `col5_p90_ui' - `col5_p10_ui'
local col5_p90p10_pred_ui = `col5_p90p10_ui' * `col5_beta_ui'
di "UI: `col5_p90p10_pred_ui'"

sum ui_max_weeks_dec21 [w = pop_2018] if col5_sample == 1, d
local col5_mean_ui = `r(mean)'

* Col 6: Drop states that raised minimum wage
reg emp_incq1_dec2021  med_2br_2018 `covid_controls_dec2021' ui_max_weeks_dec21 `dem_controls' [w = pop_2018], r

local col6_obs = e(N)
local col6_beta = _b[med_2br_2018]
local col6_se = _se[med_2br_2018]
local col6_beta_covid = _b[currcases_end2021]
local col6_se_covid = _se[currcases_end2021]
local col6_beta_ui = _b[ui_max_weeks_dec21]
local col6_se_ui = _se[ui_max_weeks_dec21]
local col6_r2 = e(r2)

gen col6_sample= e(sample)

sum med_2br_2018 [w = pop_2018] if col6_sample == 1, d
local col6_mean_rent = `r(mean)'
local col6_exp_rent = `col6_beta' * `col6_mean_rent'
local col6_p10_rent = `r(p10)'
local col6_p90_rent = `r(p90)'
local col6_p90p10_rent = `col6_p90_rent' - `col6_p10_rent'
local col6_p90p10_pred_rent = `col6_p90p10_rent' * `col6_beta'
di "Rent: `col6_p90p10_pred_rent'"

sum currcases_end2021 [w = pop_2018] if col6_sample == 1, d
local col6_mean_covid = `r(mean)'
local col6_exp_covid = `col6_beta_covid' * `col6_mean_covid'
local col6_p10_covid = `r(p10)'
local col6_p90_covid = `r(p90)'
local col6_p90p10_covid = `col6_p90_covid' - `col6_p10_covid'
local col6_p90p10_pred_covid = `col6_p90p10_covid' * `col6_beta_covid'
di "COVID: `col6_p90p10_pred_covid'"

sum ui_max_weeks_diff [w = pop_2018] if col6_sample == 1, d
local col6_mean_ui_diff = `r(mean)'
local col6_exp_ui = `col6_beta_ui' * `col6_mean_ui_diff'
local col6_p10_ui = `r(p10)'
local col6_p90_ui = `r(p90)'
local col6_p90p10_ui = `col6_p90_ui' - `col6_p10_ui'
local col6_p90p10_pred_ui = `col6_p90p10_ui' * `col6_beta_ui'
di "UI: `col6_p90p10_pred_ui'"

sum ui_max_weeks_dec21 [w = pop_2018] if col6_sample == 1, d
local col6_mean_ui = `r(mean)'

* Col 7: Use small business revenue shock (Jan - Apr 2020)
reg emp_incq1_dec2021  revenue_apr2020 `covid_controls_dec2021' ui_max_weeks_dec21 `dem_controls'  [w = pop_2018], r

local col7_obs = e(N)
local col7_beta = _b[revenue_apr2020]
local col7_se = _se[revenue_apr2020]
local col7_beta_covid = _b[currcases_end2021]
local col7_se_covid = _se[currcases_end2021]
local col7_beta_ui = _b[ui_max_weeks_dec21]
local col7_se_ui = _se[ui_max_weeks_dec21]
local col7_r2 = e(r2)

gen col7_sample= e(sample)

sum revenue_apr2020 [w = pop_2018] if col7_sample == 1, d
local col7_mean_rev = `r(mean)'
local col7_exp_rev = `col7_beta' * `col7_mean_rev'
local col7_p10_rev = `r(p10)'
local col7_p90_rev = `r(p90)'
local col7_p90p10_rev = `col7_p90_rev' - `col7_p10_rev'
local col7_p90p10_pred_rev = `col7_p90p10_rev' * `col7_beta'
di "Rent: `col7_p90p10_pred_rev'"

sum currcases_end2021 [w = pop_2018] if col7_sample == 1, d
local col7_mean_covid = `r(mean)'
local col7_exp_covid = `col7_beta_covid' * `col7_mean_covid'
local col7_p10_covid = `r(p10)'
local col7_p90_covid = `r(p90)'
local col7_p90p10_covid = `col7_p90_covid' - `col7_p10_covid'
local col7_p90p10_pred_covid = `col7_p90p10_covid' * `col7_beta_covid'
di "COVID: `col7_p90p10_pred_covid'"

sum ui_max_weeks_diff [w = pop_2018] if col7_sample == 1, d
local col7_mean_ui_diff = `r(mean)'
local col7_exp_ui = `col7_beta_ui' * `col7_mean_ui_diff'
local col7_p10_ui = `r(p10)'
local col7_p90_ui = `r(p90)'
local col7_p90p10_ui = `col7_p90_ui' - `col7_p10_ui'
local col7_p90p10_pred_ui = `col7_p90p10_ui' * `col7_beta_ui'
di "UI: `col7_p90p10_pred_ui'"

sum ui_max_weeks_dec21 [w = pop_2018] if col7_sample == 1, d
local col7_mean_ui = `r(mean)'

* Col 8: Use initial employment shock (Jan - Jul 2020)
reg emp_incq1_dec2021  emp_incq1_jul2020 `covid_controls_dec2021' ui_max_weeks_dec21 `dem_controls'  [w = pop_2018], r

local col8_obs = e(N)
local col8_beta = _b[emp_incq1_jul2020]
local col8_se = _se[emp_incq1_jul2020]
local col8_beta_covid = _b[currcases_end2021]
local col8_se_covid = _se[currcases_end2021]
local col8_beta_ui = _b[ui_max_weeks_dec21]
local col8_se_ui = _se[ui_max_weeks_dec21]
local col8_r2 = e(r2)

gen col8_sample= e(sample)

sum emp_incq1_jul2020 [w = pop_2018] if col8_sample == 1, d
local col8_mean_emp = `r(mean)'
local col8_exp_emp = `col8_beta' * `col8_mean_emp'
local col8_p10_emp = `r(p10)'
local col8_p90_emp = `r(p90)'
local col8_p90p10_emp = `col8_p90_emp' - `col8_p10_emp'
local col8_p90p10_pred_emp = `col8_p90p10_emp' * `col8_beta'
di "Emp p10: `col8_p10_emp'"
di "Emp p90: `col8_p90_emp'"
di "Emp: `col8_p90p10_pred_emp'"

sum currcases_end2021 [w = pop_2018] if col8_sample == 1, d
local col8_mean_covid = `r(mean)'
local col8_exp_covid = `col8_beta_covid' * `col8_mean_covid'
local col8_p10_covid = `r(p10)'
local col8_p90_covid = `r(p90)'
local col8_p90p10_covid = `col8_p90_covid' - `col8_p10_covid'
local col8_p90p10_pred_covid = `col8_p90p10_covid' * `col8_beta_covid'
di "COVID p10: `col8_p10_covid'"
di "COVID p90: `col8_p90_covid'"
di "COVID: `col8_p90p10_pred_covid'"

sum ui_max_weeks_diff [w = pop_2018] if col8_sample == 1, d
local col8_mean_ui_diff = `r(mean)'
local col8_exp_ui = `col8_beta_ui' * `col8_mean_ui_diff'
local col8_p10_ui = `r(p10)'
local col8_p90_ui = `r(p90)'
local col8_p90p10_ui = `col8_p90_ui' - `col8_p10_ui'
local col8_p90p10_pred_ui = `col8_p90p10_ui' * `col8_beta_ui'
di "UI p10: `col8_p10_ui'"
di "UI p90: `col8_p90_ui'"
di "UI: `col8_p90p10_pred_ui'"

sum ui_max_weeks_dec21 [w = pop_2018] if col8_sample == 1, d
local col8_mean_ui = `r(mean)'

*-------------------------------------------------------------------------------
* Export values to excel 
*-------------------------------------------------------------------------------

clear 
set obs 100

gen name = ""
replace name = "rent/rev/emp beta" if _n == 1
replace name = "rent se" if _n == 2
replace name = "covid beta" if _n == 3
replace name = "covid se" if _n == 4
replace name = "ui beta" if _n == 5
replace name = "ui se" if _n == 6
replace name = "obs" if _n == 7
replace name = "R2" if _n == 8

replace name = "implied calculation for rent/rev/emp" if _n == 9
replace name = "implied calculation for covid" if _n == 10
replace name = "implied calculation for UI" if _n == 11

replace name = "mean rent/rev/emp" if _n == 12
replace name = "mean covid" if _n == 13
replace name = "mean ui diff" if _n == 14
replace name = "mean ui" if _n == 15

replace name = "implied variance calculation for rent/rev/emp" if _n == 16
replace name = "implied variance calculation for covid" if _n == 17
replace name = "implied variance calculation for UI" if _n == 18

* Fill in coefficients and SEs
forv col = 1/8 {
	gen col`col' = `col`col'_beta' if _n == 1
	replace col`col' = `col`col'_se' if _n == 2
	replace col`col' = `col`col'_obs' if _n == 7
	replace col`col' = `col`col'_r2' if _n == 8
	
	if inrange(`col', 2, 8) {
		replace col`col' = `col`col'_beta_covid' if _n == 3
		replace col`col' = `col`col'_se_covid' if _n == 4
		replace col`col' = `col`col'_beta_ui' if _n == 5
		replace col`col' = `col`col'_se_ui' if _n == 6
	}
	
}

* Fill in change in employment explained by X
forv col = 2/6 {
    replace col`col' = `col`col'_exp_rent' if _n == 9
	replace col`col' = `col`col'_exp_covid' if _n == 10
	replace col`col' = `col`col'_exp_ui' if _n == 11

	replace col`col' = `col`col'_mean_rent' if _n == 12
	replace col`col' = `col`col'_mean_covid' if _n == 13
	replace col`col' = `col`col'_mean_ui_diff' if _n == 14
	replace col`col' = `col`col'_mean_ui' if _n == 15
	
	replace col`col' = `col`col'_p90p10_pred_rent' if _n == 16
	replace col`col' = `col`col'_p90p10_pred_covid' if _n == 17
	replace col`col' = `col`col'_p90p10_pred_ui' if _n == 18
}

replace col7 = `col7_exp_rev' if _n == 9
replace col7 = `col7_exp_covid' if _n == 10
replace col7 = `col7_exp_ui' if _n == 11

replace col7 = `col7_mean_rev' if _n == 12
replace col7 = `col7_mean_covid' if _n == 13
replace col7 = `col7_mean_ui_diff' if _n == 14
replace col7 = `col7_mean_ui' if _n == 15

replace col7 = `col7_p90p10_pred_rev' if _n == 16
replace col7 = `col7_p90p10_pred_covid' if _n == 17
replace col7 = `col7_p90p10_pred_ui' if _n == 18
	
replace col8 = `col8_exp_emp' if _n == 9
replace col8 = `col8_exp_covid' if _n == 10
replace col8 = `col8_exp_ui' if _n == 11

replace col8 = `col8_mean_emp' if _n == 12
replace col8 = `col8_mean_covid' if _n == 13
replace col8 = `col8_mean_ui_diff' if _n == 14
replace col8 = `col8_mean_ui' if _n == 15

replace col8 = `col8_p90p10_pred_emp' if _n == 16
replace col8 = `col8_p90p10_pred_covid' if _n == 17
replace col8 = `col8_p90p10_pred_ui' if _n == 18
	
* Export
export excel "${root}/results/new_table_2.xlsx", sheet(new_table_2, replace)
project, creates("${root}/results/new_table_2.xlsx")

* Scalar for people - change in emp explained by covid 
sum col3 if name == "implied calculation for covid"
local covid_expl_emp: di %2.1f abs(r(mean)) * 100

*-------------------------------------------------------------------------------
* Create folders for output numbers for paper 
*-------------------------------------------------------------------------------

cap mkdir "${root}/results"
cap mkdir "${root}/results/paper numbers"
cap mkdir "${root}/results/paper numbers/Covid"

* Output numbers
cap erase "${root}/results/paper numbers/Covid/Change in Employment Explained by Covid.yaml"

yamlout using "${root}/results/paper numbers/Covid/Change in Employment Explained by Covid.yaml",  ///
	key("covid_expl_emp") ///
	comment("Change in Employment Explained by Covid") ///
	value(`covid_expl_emp') fmt(%2.1f)
	
project, creates("${root}/results/paper numbers/Covid/Change in Employment Explained by Covid.yaml")
